{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 导库"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import os"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 处理编码"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "if not os.path.exists('filtered_data'):\n",
    "    os.mkdir('filtered_data')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = [0] * 11\n",
    "for i in range(1, 11):\n",
    "    dfs[i] = pd.read_csv(\n",
    "        f'./data/month_{\"0\" + str(i) if i / 10 != 1 else 10}.csv', encoding=\"gb2312\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1-9月份的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_data = pd.concat(dfs[1:10], ignore_index=True)\n",
    "export_file_path = './filtered_data/months1-9.csv'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10月份的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_data = pd.read_csv('./data/month_10.csv', encoding=\"gb2312\")\n",
    "export_file_path = './filtered_data/month_10.csv'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 处理“1:”问题"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "def remove_colon(x):\n",
    "    if ':' in str(x):\n",
    "        return str(x).split(':')[1]\n",
    "    return x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "for col in all_data.columns:\n",
    "    if col != '数据采集时间':\n",
    "        all_data[col] = all_data[col].apply(remove_colon)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 把数据转换为秒为单位"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "时间上，记录给出的是以分钟为单位，我们需要以秒为单位来计算，对于在同一分钟内的数据，可以将其按照采样时间间隔进行排序"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "from utils import unit_min_to_sec\n",
    "data = all_data.copy()\n",
    "data = unit_min_to_sec(data, '数据采集时间')\n",
    "# 转换为时间戳\n",
    "data['数据采集时间'] = pd.to_datetime(data['数据采集时间'])\n",
    "data = data.sort_values(by='数据采集时间', ascending=True)\n",
    "data = data.reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 保存数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total number of records: 1349\n",
      "Number of columns: 21\n",
      "\n",
      "Sample of combined data:\n"
     ]
    },
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "index",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "数据采集时间",
         "rawType": "datetime64[ns]",
         "type": "datetime"
        },
        {
         "name": "车辆状态",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "充电状态",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "车速",
         "rawType": "float64",
         "type": "float"
        },
        {
         "name": "累计里程",
         "rawType": "float64",
         "type": "float"
        },
        {
         "name": "总电压",
         "rawType": "float64",
         "type": "float"
        },
        {
         "name": "总电流",
         "rawType": "float64",
         "type": "float"
        },
        {
         "name": "SOC",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "DC-DC状态",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "绝缘电阻",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "驱动电机控制器温度",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "驱动电机转速",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "驱动电机转矩",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "驱动电机温度",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "电机控制器输入电压",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "电机控制器直流母线电流",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "电池单体电压最高值",
         "rawType": "float64",
         "type": "float"
        },
        {
         "name": "电池单体电压最低值",
         "rawType": "float64",
         "type": "float"
        },
        {
         "name": "最高温度值",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "最低温度值",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "最高报警等级",
         "rawType": "float64",
         "type": "float"
        }
       ],
       "conversionMethod": "pd.DataFrame",
       "ref": "5ac9ce41-5f37-4875-9dd6-38305624a43c",
       "rows": [
        [
         "0",
         "2022-01-01 00:00:00",
         "1",
         "3",
         "50.7",
         "206902.1",
         "368.7",
         "42.2",
         "67",
         "1",
         "5279",
         "46",
         "2887",
         "42.6",
         "57",
         "369.0",
         "40.0",
         "3.855",
         "3.822",
         "36",
         "35",
         null
        ],
        [
         "1",
         "2022-10-02 19:05:59",
         "1",
         "3",
         "50.6",
         "206902.2",
         "371.2",
         "-3.2",
         "67",
         "1",
         "5309",
         "45",
         "2885",
         "-12.8",
         "57",
         "371.0",
         "-3.1000000000000227",
         "3.883",
         "3.849",
         "36",
         "35",
         null
        ],
        [
         "2",
         "2022-10-02 19:06:00",
         "1",
         "3",
         "50.9",
         "206902.3",
         "368.9",
         "34.1",
         "67",
         "1",
         "5261",
         "45",
         "2900",
         "32.0",
         "56",
         "369.0",
         "32.299999999999955",
         "3.858",
         "3.825",
         "36",
         "35",
         null
        ],
        [
         "3",
         "2022-10-02 19:06:11",
         "1",
         "3",
         "53.8",
         "206902.5",
         "369.7",
         "26.8",
         "67",
         "1",
         "5271",
         "45",
         "3071",
         "22.3",
         "56",
         "369.0",
         "25.40000000000009",
         "3.868",
         "3.833",
         "36",
         "35",
         null
        ],
        [
         "4",
         "2022-10-02 19:06:23",
         "1",
         "3",
         "53.6",
         "206902.5",
         "369.7",
         "15.6",
         "67",
         "1",
         "5271",
         "45",
         "3056",
         "9.6",
         "56",
         "370.0",
         "14.799999999999955",
         "3.868",
         "3.833",
         "36",
         "35",
         null
        ]
       ],
       "shape": {
        "columns": 21,
        "rows": 5
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>数据采集时间</th>\n",
       "      <th>车辆状态</th>\n",
       "      <th>充电状态</th>\n",
       "      <th>车速</th>\n",
       "      <th>累计里程</th>\n",
       "      <th>总电压</th>\n",
       "      <th>总电流</th>\n",
       "      <th>SOC</th>\n",
       "      <th>DC-DC状态</th>\n",
       "      <th>绝缘电阻</th>\n",
       "      <th>...</th>\n",
       "      <th>驱动电机转速</th>\n",
       "      <th>驱动电机转矩</th>\n",
       "      <th>驱动电机温度</th>\n",
       "      <th>电机控制器输入电压</th>\n",
       "      <th>电机控制器直流母线电流</th>\n",
       "      <th>电池单体电压最高值</th>\n",
       "      <th>电池单体电压最低值</th>\n",
       "      <th>最高温度值</th>\n",
       "      <th>最低温度值</th>\n",
       "      <th>最高报警等级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-01-01 00:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>50.7</td>\n",
       "      <td>206902.1</td>\n",
       "      <td>368.7</td>\n",
       "      <td>42.2</td>\n",
       "      <td>67</td>\n",
       "      <td>1</td>\n",
       "      <td>5279</td>\n",
       "      <td>...</td>\n",
       "      <td>2887</td>\n",
       "      <td>42.6</td>\n",
       "      <td>57</td>\n",
       "      <td>369.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>3.855</td>\n",
       "      <td>3.822</td>\n",
       "      <td>36</td>\n",
       "      <td>35</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-10-02 19:05:59</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>50.6</td>\n",
       "      <td>206902.2</td>\n",
       "      <td>371.2</td>\n",
       "      <td>-3.2</td>\n",
       "      <td>67</td>\n",
       "      <td>1</td>\n",
       "      <td>5309</td>\n",
       "      <td>...</td>\n",
       "      <td>2885</td>\n",
       "      <td>-12.8</td>\n",
       "      <td>57</td>\n",
       "      <td>371.0</td>\n",
       "      <td>-3.1000000000000227</td>\n",
       "      <td>3.883</td>\n",
       "      <td>3.849</td>\n",
       "      <td>36</td>\n",
       "      <td>35</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-10-02 19:06:00</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>50.9</td>\n",
       "      <td>206902.3</td>\n",
       "      <td>368.9</td>\n",
       "      <td>34.1</td>\n",
       "      <td>67</td>\n",
       "      <td>1</td>\n",
       "      <td>5261</td>\n",
       "      <td>...</td>\n",
       "      <td>2900</td>\n",
       "      <td>32.0</td>\n",
       "      <td>56</td>\n",
       "      <td>369.0</td>\n",
       "      <td>32.299999999999955</td>\n",
       "      <td>3.858</td>\n",
       "      <td>3.825</td>\n",
       "      <td>36</td>\n",
       "      <td>35</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-10-02 19:06:11</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>53.8</td>\n",
       "      <td>206902.5</td>\n",
       "      <td>369.7</td>\n",
       "      <td>26.8</td>\n",
       "      <td>67</td>\n",
       "      <td>1</td>\n",
       "      <td>5271</td>\n",
       "      <td>...</td>\n",
       "      <td>3071</td>\n",
       "      <td>22.3</td>\n",
       "      <td>56</td>\n",
       "      <td>369.0</td>\n",
       "      <td>25.40000000000009</td>\n",
       "      <td>3.868</td>\n",
       "      <td>3.833</td>\n",
       "      <td>36</td>\n",
       "      <td>35</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-10-02 19:06:23</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>53.6</td>\n",
       "      <td>206902.5</td>\n",
       "      <td>369.7</td>\n",
       "      <td>15.6</td>\n",
       "      <td>67</td>\n",
       "      <td>1</td>\n",
       "      <td>5271</td>\n",
       "      <td>...</td>\n",
       "      <td>3056</td>\n",
       "      <td>9.6</td>\n",
       "      <td>56</td>\n",
       "      <td>370.0</td>\n",
       "      <td>14.799999999999955</td>\n",
       "      <td>3.868</td>\n",
       "      <td>3.833</td>\n",
       "      <td>36</td>\n",
       "      <td>35</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               数据采集时间  车辆状态  充电状态    车速      累计里程    总电压   总电流  SOC  DC-DC状态  \\\n",
       "0 2022-01-01 00:00:00     1     3  50.7  206902.1  368.7  42.2   67        1   \n",
       "1 2022-10-02 19:05:59     1     3  50.6  206902.2  371.2  -3.2   67        1   \n",
       "2 2022-10-02 19:06:00     1     3  50.9  206902.3  368.9  34.1   67        1   \n",
       "3 2022-10-02 19:06:11     1     3  53.8  206902.5  369.7  26.8   67        1   \n",
       "4 2022-10-02 19:06:23     1     3  53.6  206902.5  369.7  15.6   67        1   \n",
       "\n",
       "   绝缘电阻  ... 驱动电机转速 驱动电机转矩 驱动电机温度 电机控制器输入电压          电机控制器直流母线电流 电池单体电压最高值  \\\n",
       "0  5279  ...   2887   42.6     57     369.0                 40.0     3.855   \n",
       "1  5309  ...   2885  -12.8     57     371.0  -3.1000000000000227     3.883   \n",
       "2  5261  ...   2900   32.0     56     369.0   32.299999999999955     3.858   \n",
       "3  5271  ...   3071   22.3     56     369.0    25.40000000000009     3.868   \n",
       "4  5271  ...   3056    9.6     56     370.0   14.799999999999955     3.868   \n",
       "\n",
       "   电池单体电压最低值  最高温度值  最低温度值  最高报警等级  \n",
       "0      3.822     36     35     NaN  \n",
       "1      3.849     36     35     NaN  \n",
       "2      3.825     36     35     NaN  \n",
       "3      3.833     36     35     NaN  \n",
       "4      3.833     36     35     NaN  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Combined data saved to './filtered_data/month_10.csv'\n"
     ]
    }
   ],
   "source": [
    "all_data['数据采集时间'] = data['数据采集时间']\n",
    "print(f\"Total number of records: {all_data.shape[0]}\")\n",
    "print(f\"Number of columns: {all_data.shape[1]}\")\n",
    "print(\"\\nSample of combined data:\")\n",
    "display(all_data.head())\n",
    "all_data.to_csv(export_file_path,\n",
    "                index=False, encoding='utf-8')\n",
    "print(f\"\\nCombined data saved to '{export_file_path}'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "DataAnalysis",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.15"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
